forum

Home / DeveloperSection / Forums / How can reset identity seeding in SQL on ID column ?

Ask Question

How can reset identity seeding in SQL on ID column ?

Anonymous User148302-Aug-2018

Reset identity seeding in SQL  server on ID column Where ID column is auto increment.

And don't use the drop, delete or truncate command on That table.

asp.net mvcc# asp.net mvc mvc mvc4 
Updated on 18-Jun-2023
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

2 Answers

Aryan Kumar

Aryan Kumar

18-Jun-2023

There are two ways to reset identity seeding in SQL on an ID column:

  • Using the DBCC CHECKIDENT procedure: The DBCC CHECKIDENT procedure can be used to reset the identity seeding for a specific table. The following code shows how to use the DBCC CHECKIDENT procedure to reset the identity seeding for a table named MyTable:

SQL

DBCC CHECKIDENT ('MyTable', RESEED, 1);

The RESEED parameter specifies the new value for the identity seed. In this case, the new value is 1.

  • Using the TRUNCATE TABLE statement: The TRUNCATE TABLE statement can be used to truncate a table, which will reset the identity seeding for the table. The following code shows how to use the TRUNCATE TABLE statement to truncate a table named MyTable:

SQL

TRUNCATE TABLE MyTable;

Which method should you use?

The best method to use depends on your specific needs. If you only need to reset the identity seeding for a small number of rows, then the DBCC CHECKIDENT procedure is a good option. However, if you need to reset the identity seeding for a large number of rows, then the TRUNCATE TABLE statement is a better option.

Here are some things to keep in mind when resetting identity seeding:

  • Resetting identity seeding will not affect the existing rows in the table.
  • Resetting identity seeding will cause the next identity value to be 1.
  • If the table has foreign key constraints, then you may need to update the foreign key values after resetting the identity seeding.
Anonymous User

Anonymous User

03-Aug-2018
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

Hope it'll help you.
MindStick Training

Liked By